﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Healthy
{
    public partial class frmd_prenatal : Form
    {
        public frmd_prenatal()
        {
            InitializeComponent();
        }

        private void frmd_prenatal_Load(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);

            showType();
            //autoid();

        }
        private void showType()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sqlType5;
            sqlType5 = "select * from doctor ";

            SqlDataAdapter da;
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(sqlType5, Conn);
            da.Fill(ds, "doctor");

            dc.DataSource = ds.Tables["doctor"];
            dc.ValueMember = "DCID";
            dc.DisplayMember = "DCNAM";

            string sqlType4;
            sqlType4 = "select * from prenatal_care ";

            //SqlDataAdapter da;
            //DataSet ds = new DataSet();
            da = new SqlDataAdapter(sqlType4, Conn);
            da.Fill(ds, "prenatal_care");

            cmbprc.DataSource = ds.Tables["prenatal_care"];
            cmbprc.ValueMember = "PRCID";
            cmbprc.DisplayMember = "PRCID";
        }

        private void add_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            if (cmbprc.Text != "" && dc.Text != "" && we.Text != "" && he.Text != "" && desc.Text != "")
            {

                //string strOut = "";
                //strOut += "เลขที่การฝากครรภ์ :" + cmbprc.SelectedValue + "\n";
                //strOut += "แพทย์ผู้รับผิดชอบ :" + dc.SelectedValue + "\n";
                //strOut += "วันที่ตรวจ :" + dateprc.Value.ToLongDateString() + "\n";
                //strOut += "น้ำหนัก :" + we.Text + "\n";
                //strOut += "ส่วนสูง :" + he.Text + "\n";
                //strOut += "ค่าความดัน :" + textBox1.Text + "\n";
                //strOut += "รายละเอียด  :" + desc.Text + "\n";
                //strOut += "วันที่นัดครั้งต่อไป :" + datemeet.Value.ToLongDateString() + "\n";


                //MessageBox.Show(strOut, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);


                //string strdate1;
                //DateTime datet;
                //strdate1 = Convert.ToString(DateTime.Now.Year) + "-" + Convert.ToString(DateTime.Now.Month) + "-" + Convert.ToString(DateTime.Now.Day);
                //datet = dateprc.Value;
                string strdate1 = Convert.ToString(dateprc.Value.Year) + "/" + Convert.ToString(dateprc.Value.Month) + "/" + Convert.ToString(dateprc.Value.Day);//วันที่
                string strdate = Convert.ToString(datemeet.Value.Year) + "/" + Convert.ToString(datemeet.Value.Month) + "/" + Convert.ToString(datemeet.Value.Day);//วันที่


                string sql = "insert into prenatal values('" + cmbprc.SelectedValue + "' , '" + dc.SelectedValue + "' , '" + strdate1 + "','" + we.Text + "','" + he.Text + "','" + textBox1.Text+ "','" + desc.Text + "','" + strdate + "')";
                SqlCommand cmd7 = new SqlCommand(sql, Conn);
                cmd7.ExecuteNonQuery();

                //Use ExecuteNonQuery to insert data.   



                string sql1 = "SELECT PRCID,DCNAM,DATE,WEIGHT,HEIGHT,BLOOD,DIST,DATAPP FROM prenatal,doctor,person where prenatal.DCID=doctor.DCID  and prenatal.HN=person.HN ";
                SqlCommand cmd1 = new SqlCommand(sql1, Conn);
                SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
                DataSet ds = new DataSet();

                showdata1();
                FormatData1();


            }
            else
            {
                MessageBox.Show("ข้อมูลไม่ครบ", "ผิดพลาด");
            }
        }
        private void cleardata()
        {
            cmbprc.Text = "";
            dc.Text = "";
            we.Text = "";
            he.Text = "";
            textBox1.Text = "";
            desc.Text = "";
        }
        private void delete_Click(object sender, EventArgs e)
        {
            cleardata();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

          
                string sql1 = "SELECT DATE,DCNAM,WEIGHT,HEIGHT,BLOOD,DIST,DATEAPP FROM prenatal,doctor,person,prenatal_care where prenatal.DCID=doctor.DCID and prenatal_care.HN=person.HN and prenatal_care.PRCID=prenatal.PRCID and prenatal.PRCID= '" + cmbprc.SelectedValue + "'";
                SqlCommand cmd1 = new SqlCommand(sql1, Conn);
                SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
                DataSet ds = new DataSet();

                odbcDA.Fill(ds);

                this.dataGridView1.DataSource = ds.Tables[0];

                DataGridViewCellStyle cs = new DataGridViewCellStyle();
                cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);
                this.dataGridView1.ColumnHeadersDefaultCellStyle = cs;
                //this.dataGridView1.Columns[0].HeaderText = "เลขที่";
                //this.dataGridView1.Columns[1].HeaderText = "ชื่อผู้มาใช้บริการ";
                this.dataGridView1.Columns[0].HeaderText = "วันที่ตรวจ";
                this.dataGridView1.Columns[1].HeaderText = "แพทยฺผู้ตรวจ";
                this.dataGridView1.Columns[2].HeaderText = "น้ำหนัก";
                this.dataGridView1.Columns[3].HeaderText = "ส่วนสูง";
                this.dataGridView1.Columns[4].HeaderText = "ความดัน";
                this.dataGridView1.Columns[5].HeaderText = "ผลการตรวจ";
                this.dataGridView1.Columns[6].HeaderText = "วันที่นัดครั้งถัดไป";



                this.dataGridView1.Columns[0].Width = 100;
                this.dataGridView1.Columns[1].Width = 150;
                this.dataGridView1.Columns[2].Width = 100;
                this.dataGridView1.Columns[3].Width = 100;
                this.dataGridView1.Columns[4].Width = 100;
                this.dataGridView1.Columns[5].Width = 200;
                this.dataGridView1.Columns[6].Width = 100;
                //this.dataGridView1.Columns[7].Width = 100;

            //}
          
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            frmMeetpre fm = new frmMeetpre();
            fm.ShowDialog();
        }


        private void cmbprc_SelectedIndexChanged(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

              
            string sql = "select * from prenatal_care where PRCID = '" + cmbprc.SelectedValue + "'";
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            odbcDA.Fill(ds, "person");

            comboBox1.DataSource = ds.Tables["person"];
            comboBox1.ValueMember = "HN";
            comboBox1.DisplayMember = "HN";

            showdata();
        }
        private void showdata()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();


            string sql = "select * from prenatal_care where PRCID = '" + cmbprc.SelectedValue + "'";
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            odbcDA.Fill(ds, "person");

            dataGridView1.DataSource = ds.Tables["prenatal"];

            //FormatData1();
        }
        private void FormatData1()
        {

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);
            this.dataGridView1.ColumnHeadersDefaultCellStyle = cs;
            this.dataGridView1.Columns[0].HeaderText = "ชื่อผู้มาใช้บริการ";
            this.dataGridView1.Columns[1].HeaderText = "วันที่ตรวจ";
            this.dataGridView1.Columns[2].HeaderText = "น้ำหนัก";
            this.dataGridView1.Columns[3].HeaderText = "ส่วนสูง";
            this.dataGridView1.Columns[4].HeaderText = "ความดัน";
            this.dataGridView1.Columns[5].HeaderText = "ผลการตรวจ";
            this.dataGridView1.Columns[6].HeaderText = "วันที่นัดครั้งถัดไป";


            this.dataGridView1.Columns[0].Width = 100;
            this.dataGridView1.Columns[1].Width = 150;
            this.dataGridView1.Columns[2].Width = 150;
            this.dataGridView1.Columns[3].Width = 100;
            this.dataGridView1.Columns[4].Width = 80;
            this.dataGridView1.Columns[5].Width = 80;
            this.dataGridView1.Columns[6].Width = 150;
            //this.dataGridView1.Columns[7].Width = 100;
        }
        private void showdata1()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql1 = "select IDCARD,PNNAM,BIRTH,ADDRESS,GRAVIDA,DCNAM,FDATE from doctor,person,prenatal_care where  prenatal_care.DCID= doctor.DCID and person.HN = '" + comboBox1.Text + "'";
            SqlCommand cmd1 = new SqlCommand(sql1, Conn);
            SqlDataReader reader = cmd1.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                   
                    comboBox3.Text = reader["IDCARD"].ToString();
                    comboBox2.Text = reader["PNNAM"].ToString();
                    bdate.Text = reader["BIRTH"].ToString();
                    textBox3.Text = reader["ADDRESS"].ToString();
                    textBox2.Text = reader["GRAVIDA"].ToString();
                    textBox4.Text = reader["DCNAM"].ToString();
                    dateTimePicker1.Text = reader["FDATE"].ToString();


                }

                reader.Close();

            }


        }
        private void button4_Click(object sender, EventArgs e)
        {
             if (button4.Text == "")
            {
                MessageBox.Show("กรุณากรอกรายชื่อต้องการค้นหา !!!", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                 showdata1();

            }
        
        }

        private void button5_Click(object sender, EventArgs e)
        {
            frmRepre frp = new frmRepre();
            frp.ShowDialog();
        }

        private void groupBox2_Enter(object sender, EventArgs e)
        {

        }
    }
}
